#!/anaconda3/envs/FEALPy/bin python3.8
# -*- coding: utf-8 -*-
# File: data_utils.py
# Author: Bryan SHEN
# E-mail: m18801919240_3@163.com
# Site: Shanghai, China
# Time: 2024/11/1 00:31
# File-Desp:

import pandas as pd
from sqlalchemy.engine import create_engine
from datetime import timedelta
import datetime

engine = create_engine('presto://u_algorithm@trino.aihuishou.com:9090/hive/default')
current_date = datetime.datetime.now().date()  # 获取当前日期
yest_date = current_date - timedelta(days=1)  # 获取昨天的日期
current_date_str = str(current_date)


ahs_conpon_self_1515 = """
    select
        *
    from algo.ahs_self_coupon_1515
"""

ahs_conpon_self_1745 = """
    select
        *
    from algo.ahs_self_coupon_1745
"""

ahs_conpon_JD_2030 = """
    select
        *
    from algo.ahs_jd_coupon_2030
"""

# 全局变量，用于存储当前使用的优惠券数据
coupon_ahs = None
current_coupon_tag = None
current_custom_coupon_data = None

def init_coupon_data(engine, coupon_tag='SELF_1515', custom_coupon_data=None):
    """
    初始化优惠券数据（只调用一次，提高性能）
    
    Args:
        engine: 数据库引擎
        coupon_tag: 优惠券类型 ('SELF_1515', 'SELF_1745', 'JD_2030', 'customed')
        custom_coupon_data: 自定义优惠券数据（当coupon_tag='customed'时使用）
    """
    global coupon_ahs, current_coupon_tag, current_custom_coupon_data
    
    # 更新全局变量
    current_coupon_tag = coupon_tag
    current_custom_coupon_data = custom_coupon_data
    
    if coupon_tag == 'SELF_1515':
        coupon_ahs = pd.read_sql(ahs_conpon_self_1515, engine)
    elif coupon_tag == 'SELF_1745':
        coupon_ahs = pd.read_sql(ahs_conpon_self_1745, engine)
    elif coupon_tag == 'JD_2030':
        coupon_ahs = pd.read_sql(ahs_conpon_JD_2030, engine)
    elif coupon_tag == 'customed':
        # 使用用户自定义的优惠券数据
        if custom_coupon_data is None:
            raise ValueError("自定义优惠券数据不能为空")
        coupon_ahs = pd.DataFrame(custom_coupon_data)
    else:
        raise ValueError(f"不支持的优惠券类型: {coupon_tag}")
    
    # 确保数据类型正确
    coupon_ahs['coupon_type'] = coupon_ahs['coupon_type'].astype(int)
    coupon_ahs['price_range_min'] = coupon_ahs['price_range_min'].astype(int)
    coupon_ahs['price_range_max'] = coupon_ahs['price_range_max'].astype(int)
    coupon_ahs['coupon_price'] = coupon_ahs['coupon_price'].astype(int)
    
    print(f"优惠券数据初始化完成: {coupon_tag}, 共 {len(coupon_ahs)} 条记录")

def get_ahs_coupon_data(engine, coupon_tag='SELF_1515', custom_coupon_data=None):
    """
    获取AHS优惠券数据（保持向后兼容，但建议使用init_coupon_data）
    
    Args:
        engine: 数据库引擎
        coupon_tag: 优惠券类型 ('SELF_1515', 'SELF_1745', 'JD_2030', 'customed')
        custom_coupon_data: 自定义优惠券数据（当coupon_tag='customed'时使用）
    
    Returns:
        pd.DataFrame: 优惠券数据
    """
    # 保持向后兼容，但建议使用init_coupon_data
    global coupon_ahs, current_coupon_tag, current_custom_coupon_data
    
    # 如果参数相同且已有数据，直接返回
    if (current_coupon_tag == coupon_tag and current_custom_coupon_data == custom_coupon_data and coupon_ahs is not None):
        return coupon_ahs
    
    # 否则重新初始化
    init_coupon_data(engine, coupon_tag, custom_coupon_data)
    return coupon_ahs

# 初始化默认优惠券数据（保持向后兼容）
try:
    init_coupon_data(engine, 'SELF_1515', None)
except Exception as e:
    print(f"初始化默认优惠券数据失败: {e}")
    coupon_ahs = None


# 2025-08-06 AHS券后价获取方式
def calculate_ahs_couponed_price(price, coupon_data=None):
    """ 计算爱回收的券后价 """
    
    # 优先使用全局的coupon_ahs（性能优化）
    if coupon_ahs is not None:
        coupon_data = coupon_ahs
    elif coupon_data is None:
        raise ValueError("优惠券数据未初始化，请先调用init_coupon_data函数")

    coupon = 0
    rated_price = price
    for _, row in coupon_data.iterrows():
        if row['coupon_type'] == 0:  # 阶梯型券
            if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                coupon = row['coupon_price']
        elif row['coupon_type'] == 1:  # 固定比例券
            if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                rated_price = price * (1 + row['coupon_price'])  # 小数比例计算
    return coupon + rated_price


# 2025-08-06 AHS中间策略价获取方式
def calculate_strategy_value(price, coupon_data=None):
    """ 计算 <转转追价-策略-中间值> """
    
    # 优先使用全局的coupon_ahs（性能优化）
    if coupon_ahs is not None:
        coupon_data = coupon_ahs
    elif coupon_data is None:
        raise ValueError("优惠券数据未初始化，请先调用init_coupon_data函数")

    for _, row in coupon_data.iterrows():
        # 2025-04-08 AHS除了阶梯型券，也新增设比例券
        if row['coupon_type'] == 0:  # 阶梯型券
            if row['price_range_min'] <= price <= row['price_range_max'] + row['coupon_price']:
                return price - row['coupon_price']
        elif row['coupon_type'] == 1:  # 固定比例券
            if row['price_range_min'] <= price <= row['price_range_max'] * (1 + row['coupon_price']):
                return price / (1 + row['coupon_price'])
    return price


# 2025-02-21 从之前写死的券逻辑更新为随数据库实时更新的版本
def mapping_coupon_price_boundary(row, coupon_data=None):
    """ 替换写死的ahs优惠券边界逻辑 """
    
    # 优先使用全局的coupon_ahs（性能优化）
    if coupon_ahs is not None:
        coupon_data = coupon_ahs
    elif coupon_data is None:
        raise ValueError("优惠券数据未初始化，请先调用init_coupon_data函数")

    price = row['zz_couponed_price_18_added']

    min_boundary = coupon_data['price_range_min'].min()
    max_boundary = coupon_data['price_range_min'].max()

    if price < min_boundary:
        return 0
    for _, row in coupon_data.iterrows():
        # 2025-04-08 AHS除了阶梯型券，也新增设比例券
        if row['coupon_type'] == 0:  # 阶梯型券
            if row['price_range_min'] <= price <= row['price_range_max'] + row['coupon_price']:
                return int(row['price_range_min'])
        elif row['coupon_type'] == 1:  # 固定比例券
            if row['price_range_min'] <= price <= row['price_range_max'] * (1 + row['coupon_price']):
                return int(row['price_range_min'])

    return max_boundary

# 2025-07-22 转转券表更新
zz_conpon_sql = """
    select 
        * 
    from 
        rpt.rpt_platform_trade_price_compass_coupon_package_zz
    where 
          ahs_category_id = 1
        and create_date = date '{}'
""".format(current_date_str)
coupon_zz = pd.read_sql(zz_conpon_sql, engine)


# 2025-08-05 对于多个分组的门槛券 只保留最新一组的结果
def keep_latest_base_coupons(df: pd.DataFrame) -> pd.DataFrame:
    """
    仅保留 coupon_price_type_id = 0 且 coupon_type_name = '基础券' 中，
    valid_time 起始日期最大的一组记录，其余记录保持不变。
    """
    # —— 1️⃣ 找出“基础阶梯券”子集 ——
    mask_base = (df["coupon_price_type_id"] == 0) & (df["coupon_type_name"] == "基础券")
    base_df = df.loc[mask_base].copy()

    # —— 2️⃣ 解析 valid_time 起始日期并找出最新日期 ——
    base_df["start_date"] = pd.to_datetime(base_df["valid_time"].str.split("~").str[0])
    latest_start_date = base_df["start_date"].max()

    # —— 3️⃣ 仅保留起始日期 = latest_start_date 的记录 ——
    base_df = base_df[base_df["start_date"] == latest_start_date].drop(columns="start_date")

    # —— 4️⃣ 与其余记录合并返回 ——
    remaining_df = df.loc[~mask_base]
    return pd.concat([remaining_df, base_df], ignore_index=True)

coupon_zz_filtered = keep_latest_base_coupons(coupon_zz)


# 2025-07-21 转转券逻辑更新
def calculate_zz_couponed_price(price: float) -> int:
    """
    根据 coupon_zz DataFrame 计算转转的券后价。

    逻辑：
    1. 分别从 coupon_zz 中筛选“基础券”（coupon_type_name='基础券'）和“叠加券”（coupon_type_name='叠加券'）。
    2. 对每种券再分门槛券（coupon_price_type_id=0）和比例券（coupon_price_type_id=1）两类：
       — 门槛券：取所有 price_range_min <= price 的行，优惠额取 coupon_price 的最大值；
       — 比例券：取所有 price_range_min <= price 的行，比例取 coupon_price 的最大值，再计算 int(price * 比例) 作为优惠额。
    3. 每种券（基础券/叠加券）内部，门槛券和比例券的优惠额取最大值。
    4. 最终券后价 = price - 基础券优惠 - 叠加券优惠，结果向下取整整数返回。
    """

    # 筛基础券和叠加券
    # base_df = coupon_zz[coupon_zz['coupon_type_name'] == '基础券']
    # added_df = coupon_zz[coupon_zz['coupon_type_name'] == '叠加券']
    base_df = coupon_zz_filtered[coupon_zz_filtered['coupon_type_name'] == '基础券']
    added_df = coupon_zz_filtered[coupon_zz_filtered['coupon_type_name'] == '叠加券']

    def _threshold_discount(df):
        """ 门槛券 """
        thr = df[df['coupon_price_type_id'] == 0]
        # 取所有 price_range_min <= price 的行，coupon_price 最大值
        valid = thr[thr['price_range_min'] <= price]
        return float(valid['coupon_price'].max()) if not valid.empty else 0.0

    def _rate_discount(df):
        """ 比例券 """
        rate = df[df['coupon_price_type_id'] == 1]
        # 取所有 price_range_min <= price 的行，coupon_price 最大值
        valid = rate[rate['price_range_min'] <= price]
        if valid.empty:
            return 0
        max_rate = float(valid['coupon_price'].max())
        return int(price * max_rate)  # 向下取整

    # 计算基础券优惠
    base_thr = _threshold_discount(base_df)  # 门槛券
    base_rate = _rate_discount(base_df)  # 比例券
    base_disc = max(base_thr, base_rate)

    # 计算叠加券优惠
    add_thr = _threshold_discount(added_df)
    add_rate = _rate_discount(added_df)
    add_disc = max(add_thr, add_rate)

    # 券后价 = price + 基础券优惠 + 叠加券优惠
    return int(price + base_disc + add_disc)
    

# 十位取整逻辑（取最后两位数）
def rounding_to_tens(price):

    # if price == 0:
    #     return 0
    if 0 <= price < 1:
        return 1
    elif 1 <= price < 40:
        return 40
    elif 40 <= price < 50:
        return 50
    elif 50 <= price < 60:
        return 60
    elif price >= 60:
        return 100


def calculate_strategy_quote_price(row):

    try:
        if row['券后价差-ahs-转转'] < 0:

            # 1，将 '转转追价-策略-中间值' 的十位结果转换之后的价格 (eg. 2160)
            int_part = (row['转转追价-策略-中间值'] // 100) * 100  # 获取百位及以上值，这里是：2100
            mod_part = row['转转追价-策略-中间值'] % 100  # 获取十位及以下值，这里是：60
            tens = rounding_to_tens(mod_part)  # 获取十位取整结果，这里是：100
            lookup_value1 = int_part + tens

            # 2，将 '转转新人券后价' 映射到AHS优惠券的 “门槛价格”
            lookup_value2 = mapping_coupon_price_boundary(row)  # 获取优惠券边界值，这里是：2000

            # 1和2为什么取两者中的最大值
            return max(lookup_value1, lookup_value2)  # max(2100+100, 2000) = 2200
        else:
            # 3，AHS券前价
            # return row['ahs_ori_price']
            return row['finalprice']
    except:

        return 0


# 十位取整逻辑（取最后两位数）
def rounding_to_tens(price):

    # if price == 0:
    #     return 0
    if 0 <= price < 1:
        return 1
    elif 1 <= price < 40:
        return 40
    elif 40 <= price < 50:
        return 50
    elif 50 <= price < 60:
        return 60
    elif price >= 60:
        return 100


def calculate_strategy_quote_price(row):

    try:
        if row['券后价差-ahs-转转'] < 0:

            # 1，将 '转转追价-策略-中间值' 的十位结果转换之后的价格 (eg. 2160)
            int_part = (row['转转追价-策略-中间值'] // 100) * 100  # 获取百位及以上值，这里是：2100
            mod_part = row['转转追价-策略-中间值'] % 100  # 获取十位及以下值，这里是：60
            tens = rounding_to_tens(mod_part)  # 获取十位取整结果，这里是：100
            lookup_value1 = int_part + tens

            # 2，将 '转转新人券后价' 映射到AHS优惠券的 “门槛价格”
            lookup_value2 = mapping_coupon_price_boundary(row)  # 获取优惠券边界值，这里是：2000

            # 1和2为什么取两者中的最大值
            return max(lookup_value1, lookup_value2)  # max(2100+100, 2000) = 2200
        else:
            # 3，AHS券前价
            return row['finalprice']
    except:

        return 0


# 2025-04-27 从smooth price开始对后两位进行修正
def smooth_price_tens(price):
    """ 平滑价格的修正区间 """

    if price == 0:
        return 0
    elif 0 < price < 40:
        return 0
    elif 40 <= price < 50:
        return 40
    elif 50 <= price < 60:
        return 50
    elif 60 <= price < 70:
        return 60
    elif price >= 70:
        return 100

def modify_tens_price(price):
    """ 平滑价格的修正逻辑 """

    # 将的十位结果转换之后的价格 (eg. 2163)
    int_part = (price // 100) * 100  # 获取百位及以上值，这里是：2100
    mod_part = price % 100  # 获取十位及以下值，这里是：63
    tens = smooth_price_tens(mod_part)  # 获取十位取整结果，这里是：60

    return int_part + tens  # 2160


# 定义等级顺序
level_order = [
    'A+', 'A-', 'A1', 'A', 'A2', 'A3', 'A4',
    'B+1', 'B+2', 'B1', 'B', 'B2',
    'C+1', 'C+', 'C+2', 'C1', 'C', 'C2',
    'D+1', 'D+', 'D+2', 'D1', 'D', 'D2'
]

level_hierarchy = [
    ("A+", "A-"),
    ("A+", "A1"),
    ("A+", "A"),
    ("A-", "A2"),
    ("A1", "A2"),
    ("A", "A2"),
    ("A2", "A3"),  # 2025-09-01
    ("A2", "A4"),  # 2025-09-01
    ("A-", "B+1"),
    ("B+1", "B+2"),
    ("A1", "B1"),
    ("B1", "B"),
    ("B1", "B2"),
    ("B+1", "B2"),
    ("A", "C+1"),
    ("A", "C+"),
    ("B1", "C+2"),
    ("C+1", "C1"),
    ("C+", "C1"),
    ("B+1", "C1"),
    ("B1", "C1"),
    ("B2", "C1"),  # 2025-03-26
    ("C+2", "C"),
    ("C+1", "C"),
    ("C+", "C"),
    ("B+1", "C"),
    ("C", "C2")
]


def check_price_inversion(df, sku, parent, child, price_column):

    parent_price = df.loc[(df['product_sku_name'] == sku) & (df['product_level_name'] == parent), price_column]
    child_price = df.loc[(df['product_sku_name'] == sku) & (df['product_level_name'] == child), price_column]

    # 父价格和子价格都不为空的情况下，子价格 > 父价格 时，出现倒挂 +1
    if not parent_price.empty and not child_price.empty:
        # return parent_price.values[0] <= child_price.values[0]
        return parent_price.values[0] < child_price.values[0]  # 2025-02-18 去掉=
    return False


def calculate_inversion_statistics(df, price_column):
    """ 计算倒挂率"""

    results = []

    # 计算倒挂率
    relationships_df = pd.DataFrame(level_hierarchy, columns=['parent_level', 'child_level'])

    # Iterate through each unique SKU
    for sku in df['product_sku_name'].unique():
        total_comparisons = 0
        inversion_count = 0
        # Iterate through each parent-child relationship to check inversions
        for _, row in relationships_df.iterrows():
            parent_level = row['parent_level']
            child_level = row['child_level']
            if (df['product_sku_name'] == sku).any() and \
                    (df['product_level_name'] == parent_level).any() and \
                    (df['product_level_name'] == child_level).any():
                total_comparisons += 1
                if check_price_inversion(df, sku, parent_level, child_level, price_column):
                    inversion_count += 1
        # Append result for this SKU
        results.append({
            'product_sku_name': sku,
            'total_comparisons': total_comparisons,
            'inversion_count': inversion_count
        })

    return pd.DataFrame(results)


def define_competiitive_starts_ends(human_competitive_rate):
    """ 系统苹果型号目前所用数据 """

    starts, ends = 0, 0
    if human_competitive_rate <= 0.0:
        starts, ends = 0, 0
    elif 0.0 < human_competitive_rate <= 0.1:
        # starts = 0
        # ends = 15
        starts = 0
        ends = 150
    elif 0.1 < human_competitive_rate < 0.8:
        # starts = int((human_competitive_rate - 0.1) * 100)
        # ends = int((human_competitive_rate + 0.2) * 100)
        starts = int((human_competitive_rate - 0.1) * 1000)
        ends = int((human_competitive_rate + 0.2) * 1000)
    elif human_competitive_rate >= 0.8:
        starts = 790
        ends = 990

    return starts, ends


# BI: 计算 "修差异过大后券前价格" 字段价格
def calculate_big_gap_value(row):
    """ 基于不同等级，考虑不同row['同sku的A+券前价']区间以及不同row['价差比']的调价逻辑
        涉及大量参数：都是业务根据‘属性项/值’的实际情况给出的参考值
    """

    if row['product_level_name'] == "A1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A-":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A2":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.74, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.18:
            return round(row['同sku的A+券前价'] * 0.825, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.16:
            return round(row['同sku的A+券前价'] * 0.845, 0)
    elif row['product_level_name'] == "B1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.35:
            return round(row['同sku的A+券前价'] * 0.655, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.705, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.25:
            return round(row['同sku的A+券前价'] * 0.755, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.805, 0)
    elif row['product_level_name'] == "A":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "B2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
        elif row['同sku的A+券前价'] >= 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
    elif row['product_level_name'] == "C+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.33:
            return round(row['同sku的A+券前价'] * 0.675, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
    elif row['product_level_name'] == "C1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
    elif row['product_level_name'] == "D+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.48:
            return round(row['同sku的A+券前价'] * 0.525, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.45:
            return round(row['同sku的A+券前价'] * 0.555, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.4:
            return round(row['同sku的A+券前价'] * 0.605, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
    # 下面3个为新增等级
    elif row['product_level_name'] == "B+1":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.71, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.745, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.815, 0)
    elif row['product_level_name'] == "B+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.7, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        # elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.21:
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
    elif row['product_level_name'] == "D+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.46:
            return round(row['同sku的A+券前价'] * 0.545, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)

    return round(row['adjusted_price'], 0)


# human: 计算 "修差异过大后券前价格" 字段价格
def calculate_big_gap_value_human(row):
    """ 基于不同等级，考虑不同row['同sku的A+券前价']区间以及不同row['价差比']的调价逻辑
        涉及大量参数：都是业务根据‘属性项/值’的实际情况给出的参考值
    """

    if row['product_level_name'] == "A1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A-":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A2":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.74, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.18:
            return round(row['同sku的A+券前价'] * 0.825, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.16:
            return round(row['同sku的A+券前价'] * 0.845, 0)
    elif row['product_level_name'] == "B1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.35:
            return round(row['同sku的A+券前价'] * 0.655, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.705, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.25:
            return round(row['同sku的A+券前价'] * 0.755, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.805, 0)
    elif row['product_level_name'] == "A":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "B2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
        elif row['同sku的A+券前价'] >= 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
    elif row['product_level_name'] == "C+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.33:
            return round(row['同sku的A+券前价'] * 0.675, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
    elif row['product_level_name'] == "C1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
    elif row['product_level_name'] == "D+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.48:
            return round(row['同sku的A+券前价'] * 0.525, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.45:
            return round(row['同sku的A+券前价'] * 0.555, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.4:
            return round(row['同sku的A+券前价'] * 0.605, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
    # 下面3个为新增等级
    elif row['product_level_name'] == "B+1":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.71, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.745, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.815, 0)
    elif row['product_level_name'] == "B+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.7, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        # elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.21:
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
    elif row['product_level_name'] == "D+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.46:
            return round(row['同sku的A+券前价'] * 0.545, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)

    return round(row['human_price'], 0)


# human: 计算 "修差异过大后券前价格" 字段价格
def calculate_big_gap_value_human_g2(row):
    """ 基于不同等级，考虑不同row['同sku的A+券前价']区间以及不同row['价差比']的调价逻辑
        涉及大量参数：都是业务根据‘属性项/值’的实际情况给出的参考值
    """

    if row['product_level_name'] == "A1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A-":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A2":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.74, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.18:
            return round(row['同sku的A+券前价'] * 0.825, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.16:
            return round(row['同sku的A+券前价'] * 0.845, 0)
    elif row['product_level_name'] == "B1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.35:
            return round(row['同sku的A+券前价'] * 0.655, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.705, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.25:
            return round(row['同sku的A+券前价'] * 0.755, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.805, 0)
    elif row['product_level_name'] == "A":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "B2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
        elif row['同sku的A+券前价'] >= 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
    elif row['product_level_name'] == "C+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.33:
            return round(row['同sku的A+券前价'] * 0.675, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
    elif row['product_level_name'] == "C1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
    elif row['product_level_name'] == "D+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.48:
            return round(row['同sku的A+券前价'] * 0.525, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.45:
            return round(row['同sku的A+券前价'] * 0.555, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.4:
            return round(row['同sku的A+券前价'] * 0.605, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
    # 下面3个为新增等级
    elif row['product_level_name'] == "B+1":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.71, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.745, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.815, 0)
    elif row['product_level_name'] == "B+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.7, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        # elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.21:
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
    elif row['product_level_name'] == "D+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.46:
            return round(row['同sku的A+券前价'] * 0.545, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)

    return round(row['human_price'], 0)


# 生成新字段 "转转追价-策略报价-券后"
def calculate_strategy_couponed_price(price):

    if price < 200:
        return price
    elif 200 <= price < 400:
        return price + 40
    elif 400 <= price < 800:
        return price + 80
    elif 800 <= price < 1200:
        return price + 150
    elif 1200 <= price < 2000:
        return price + 220
    elif 2000 <= price < 3000:
        return price + 320
    elif 3000 <= price < 4800:
        return price + 450
    else:
        return price + 500

